use ManLab
go
create view view_ReportDetails as 
	select r.reportID, r.report, r.testDate, r.status, p.pID, p.pFName, p.pLName, p.pAddress, p.pPhone, p.pGender, p.pDOB,
			t.testID, t.testName, s.staffID, s.staffFName, s.staffLName, s.staffAddress, s.staffPhone, s.staffGender, s.staffEduQual, s.staffTechSkill,
			d.dID, d.dFName, dLName, d.dAddress, d.dPhone, d.dSpecialization
			from Report as r inner join Test as t on r.testID=t.testID inner join StaffDetail as s on r.staffID=s.staffID 
				inner join PatientDetail as p on r.pID=p.pID inner join DoctorDetail as d on r.dID=d.dID
go
create view view_BillingDetails as
	select bd.bID, b.bDate, b.bAmount, b.pID, p.pFName, p.pLName, p.pAddress, p.pPhone, p.pGender, p.pDOB,
			bd.testID, t.testName, bd.testCost 
			from BillDetail as bd inner join Billing as b on bd.bID=b.bID inner join Test as t on bd.testID=t.testID inner join PatientDetail as p on p.pID=b.pID
go
create view view_TestItemRequire as
	select i.testID, t.testName,i.iNoRequire, s.sName, s.sAvailable, s.sReuseable
			from ItemRequire as i inner join Test as t on i.testID=t.testID inner join StockDetail as s on i.sID=s.sID